﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

using VK.Comm;
using VK.Comm.TransactSql;

namespace VK.Casemeet.Lines
{
    internal static class ScheduleDAL
    {
        internal static ScheduleEntity GetLineScheduleByReader(IDataReader dr)
        {
            ScheduleEntity lineschedule = new ScheduleEntity();

            lineschedule.ScheduleID = dr.IsDBNull(0) ? 0 : dr.GetInt32(0);
            lineschedule.LineID = dr.IsDBNull(1) ? 0 : dr.GetInt32(1);
            lineschedule.DepartureDate = Util.ConverDateTime(dr.GetValue(2));

            return lineschedule;
        }

        internal static int GetLineScheduleCount(IList<Condition> conditionArray)
        {
            Database db = DatabaseFactory.CreateDatabase();
            int result = 0;

            string SelectLineSchedule = @"SELECT COUNT([ScheduleID]) FROM [LineSchedule]";

            List<DbParameter> parameterlst = new List<DbParameter>();
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectLineSchedule, ref parameterlst);
            DbCommand SelectLineScheduleCommand = db.GetSqlStringCommand(SelectLineSchedule);
            DbHelper.PackDbCommandByDbParameters(SelectLineScheduleCommand, parameterlst.ToArray());

            object obj = db.ExecuteScalar(SelectLineScheduleCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());

            return result;
        }

        internal static List<ScheduleEntity> GetLineScheduleList(int startRecordIndex, int endRecordIndex, IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<ScheduleEntity> lineschedulelst = new List<ScheduleEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectLineSchedule = @"
                    SELECT [ScheduleID],[LineID],[DepartureDate]
                    FROM (
                        SELECT [ScheduleID],[LineID],[DepartureDate], 
                            ROW_NUMBER() OVER (ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectLineSchedule += " " + sortField + " " + sortMode + ",";
            SelectLineSchedule += @" [LineSchedule].[ScheduleID] Desc
                    ) AS rowNum 
                        FROM [LineSchedule] ";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectLineSchedule, ref parameterlst);
            SelectLineSchedule += @"
                    ) AS [LineSchedule] 
                    WHERE rowNum BETWEEN @StartRecordIndex and @EndRecordIndex ";

            DbCommand SelectLineScheduleCommand = db.GetSqlStringCommand(SelectLineSchedule);
            parameterlst.Add(DbHelper.PackInParameter(db, "@StartRecordIndex", DbType.Int32, startRecordIndex));
            parameterlst.Add(DbHelper.PackInParameter(db, "@EndRecordIndex", DbType.Int32, endRecordIndex));
            DbHelper.PackDbCommandByDbParameters(SelectLineScheduleCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectLineScheduleCommand))
            {
                while (dr.Read())
                {
                    lineschedulelst.Add(GetLineScheduleByReader(dr));
                }
            }

            return lineschedulelst;
        }

        internal static List<ScheduleEntity> GetLineScheduleList(IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<ScheduleEntity> lineschedulelst = new List<ScheduleEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectLineSchedule = @"
                    SELECT [ScheduleID],[LineID],[DepartureDate]
                    FROM [LineSchedule] ";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectLineSchedule, ref parameterlst);
            SelectLineSchedule += " ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectLineSchedule += " " + sortField + " " + sortMode + ",";
            SelectLineSchedule += " [LineSchedule].[ScheduleID] Desc ";

            DbCommand SelectLineScheduleCommand = db.GetSqlStringCommand(SelectLineSchedule);
            DbHelper.PackDbCommandByDbParameters(SelectLineScheduleCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectLineScheduleCommand))
            {
                while (dr.Read())
                {
                    lineschedulelst.Add(GetLineScheduleByReader(dr));
                }
            }

            return lineschedulelst;
        }

        internal static ScheduleEntity GetLineSchedule(int linescheduleID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            ScheduleEntity lineschedule = null;

            string SelectLineSchedule = @"
                    SELECT [ScheduleID],[LineID],[DepartureDate]
                    FROM [LineSchedule] 
                    WHERE [LineSchedule].[ScheduleID]=@ScheduleID
                ";

            DbCommand SelectLineScheduleCommand = db.GetSqlStringCommand(SelectLineSchedule);
            db.AddInParameter(SelectLineScheduleCommand, "@ScheduleID", DbType.Int32, linescheduleID);

            using (IDataReader dr = db.ExecuteReader(SelectLineScheduleCommand))
            {
                if (dr.Read())
                {
                    lineschedule = GetLineScheduleByReader(dr);
                }
            }

            return lineschedule;
        }

        internal static int InsertLineSchedule(ScheduleEntity lineschedule)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string InsertLineSchedule = @"INSERT INTO [LineSchedule] 
				([ScheduleID],[LineID],[DepartureDate])
				 VALUES (@ScheduleID,@LineID,@DepartureDate)";

            DbCommand InsertLineScheduleCommand = db.GetSqlStringCommand(InsertLineSchedule);
            db.AddInParameter(InsertLineScheduleCommand, "@ScheduleID", DbType.Int32, lineschedule.ScheduleID);
            db.AddInParameter(InsertLineScheduleCommand, "@LineID", DbType.Int32, lineschedule.LineID);
            db.AddInParameter(InsertLineScheduleCommand, "@DepartureDate", DbType.DateTime, lineschedule.DepartureDate);

            result = db.ExecuteNonQuery(InsertLineScheduleCommand);

            return result;
        }

        internal static int UpdateLineSchedule(ScheduleEntity lineschedule)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();
            string UpdateLineSchedule = @"UPDATE [LineSchedule] 
				 SET [ScheduleID]=@ScheduleID,[LineID]=@LineID,[DepartureDate]=@DepartureDate
				 WHERE [ScheduleID] = @ScheduleID";

            DbCommand UpdateLineScheduleCommand = db.GetSqlStringCommand(UpdateLineSchedule);
            db.AddInParameter(UpdateLineScheduleCommand, "@ScheduleID", DbType.Int32, lineschedule.ScheduleID);
            db.AddInParameter(UpdateLineScheduleCommand, "@LineID", DbType.Int32, lineschedule.LineID);
            db.AddInParameter(UpdateLineScheduleCommand, "@DepartureDate", DbType.DateTime, lineschedule.DepartureDate);

            result = db.ExecuteNonQuery(UpdateLineScheduleCommand);

            return result;
        }

        internal static int DeleteLineSchedule(int linescheduleID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string DeleteLineSchedule = @"
                DELETE FROM [LineSchedule] WHERE ScheduleID=@ScheduleID";

            DbCommand DeleteLineScheduleCommand = db.GetSqlStringCommand(DeleteLineSchedule);
            db.AddInParameter(DeleteLineScheduleCommand, "@ScheduleID", DbType.Int32, linescheduleID);
            result = db.ExecuteNonQuery(DeleteLineScheduleCommand);

            return result;
        }

        internal static int SetLineScheduleEnable(int linescheduleID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SetLineSchedule = @"UPDATE [LineSchedule]
                    SET [IsEnable] = ([IsEnable]+1)%2
                    WHERE [ScheduleID] = @ScheduleID";

            DbCommand SetLineScheduleCommand = db.GetSqlStringCommand(SetLineSchedule);
            db.AddInParameter(SetLineScheduleCommand, "@ScheduleID", DbType.Int32, linescheduleID);
            result = db.ExecuteNonQuery(SetLineScheduleCommand);

            return result;
        }

        internal static int HitLineSchedule(int linescheduleID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SetLineSchedule = @"UPDATE [LineSchedule]
                    SET [Hits] = [Hits]+1
                    WHERE [ScheduleID] = @ScheduleID";

            DbCommand SetLineScheduleCommand = db.GetSqlStringCommand(SetLineSchedule);
            db.AddInParameter(SetLineScheduleCommand, "@ScheduleID", DbType.Int32, linescheduleID);
            result = db.ExecuteNonQuery(SetLineScheduleCommand);

            return result;
        }
    }
}
